package com.ihelp.rest.mis.platform.dao;

import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.ihelp.rest.base.hibernate4.BaseHibernateDao;
import com.ihelp.rest.base.po.Page;
import com.ihelp.rest.mis.pojo.Abnormaltime;
import com.ihelp.rest.mis.pojo.UserRecord;
import com.ihelp.rest.mis.vo.UserRecordVO;

@Repository
public class UserRecordDao  extends BaseHibernateDao<UserRecord, String>{

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	/*public Page<UserRecord> queryRecordList(int page,int rows){
		String hql = "from UserRecord ";
		return this.page(hql, page, rows);
	}*/
	
	
	public Page<UserRecord> getAllRecordList(int start, int pageSize,String computername,String qtime){
		StringBuilder hql = new StringBuilder();
		hql.append("from UserRecord where 1=1 " );
		if(StringUtils.isNotBlank(computername)){
			hql.append(" and computername like '"+computername+"%' ");
		}
		if(StringUtils.isNotBlank(qtime)){
			hql.append(" and to_char(to_date(browsetime,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') like '"+qtime+"%' ");
		}
		hql.append("order by to_date(browsetime,'yyyy-mm-dd hh24:mi:ss') ");
		return this.page(hql.toString(), start, pageSize);
	}
	
	public void dosaveUserRecord(List<UserRecord> datalist){
		/*for(int i=0;i<datalist.size();i++){
			StringBuilder sql = new StringBuilder();
			UserRecord tmp_ur =  datalist.get(i);
			sql.append("insert into userrecord(id,browsetime,computername,computergroup,controluser,usergroup,domain,title,browser,url,addtime) values(" );
			sql.append("sys_guid()," );
			sql.append("'"+tmp_ur.getBrowsetime()+"'," );
			sql.append("'"+tmp_ur.getComputername()+"'," );
			sql.append("'"+tmp_ur.getComputergroup()+"'," );
			sql.append("'"+tmp_ur.getControluser()+"'," );
			sql.append("'"+tmp_ur.getUsergroup()+"'," );
			sql.append("'"+tmp_ur.getDomain()+"'," );
			sql.append("'"+tmp_ur.getTitle()+"'," );
			sql.append("'"+tmp_ur.getBrowser()+"'," );
			sql.append("'"+tmp_ur.getUrl()+"'," );
			sql.append("'"+tmp_ur.getAddtime()+"') " );
			this.jdbcTemplate.execute(sql.toString());  
		} 
		for(UserRecord ur : datalist){
			this.save(ur);
		}*/
		
		for(int i=0;i<datalist.size();i++){
			UserRecord ur = datalist.get(i);
			this.save(ur);
			if(i%100==0){
				this.flush();
				this.clear();
			}
		}
	}
	/**
	 * 
	 * @param datalist
	 * @param queryPara
	 * @param usercode
	 * @param begin
	 * @param end
	 * @return
	 */
	public Page<UserRecordVO> getYCSW(Page<UserRecordVO> datalist,List<Abnormaltime> queryPara,String usercode,String begin,String end){
		StringBuilder sql = new StringBuilder(); 
		sql.append("select * from ( ");
		for(int i=0;i<queryPara.size();i++){
			Abnormaltime ab = queryPara.get(i);
			sql.append("select browsetime,title, url,get_titletype(title,url) TYPENAME from userrecord   " +
					"where  (trim(substr(browsetime,instr(browsetime,' ')))  between '"+ab.getBegintime()+"' and '"+ab.getEndtime()+"' or  is_abnormal(url) <> 0 )" +
							" and to_char(to_date(substr(browsetime,0,10),'yyyy-mm-dd'),'yyyy-mm-dd') between '"+begin+"' and '"+end+"' "+
							" and computername like '"+usercode+"%'  ");
			//if(i+1<queryPara.size()){
				sql.append(" union all ");
			//}
		}
		sql.append(" select browsetime,title, url,get_titletype(title,url) TYPENAME from userrecord where  is_abnormal_title(title,'') >0 " +
				" and to_char(to_date(substr(browsetime,0,10),'yyyy-mm-dd'),'yyyy-mm-dd') between '"+begin+"' and '"+end+"' " +
				" and computername like '"+usercode+"%'   ");
		sql.append(" ) order by browsetime desc ") ;
		return  this.findSqlPage(datalist, sql.toString(),UserRecordVO.class);
	}
	
	
	public List<Map<String,Object>> getYCSW(List<Abnormaltime> queryPara,String usercode,String begin,String end){
		StringBuilder sql = new StringBuilder();
		sql.append("select * from ( ");
		for(int i=0;i<queryPara.size();i++){
			Abnormaltime ab = queryPara.get(i);
			sql.append("select browsetime,title, url,get_titletype(title,url) TYPENAME from userrecord " +
					"where  (trim(substr(browsetime,instr(browsetime,' ')))  between '"+ab.getBegintime()+"' and '"+ab.getEndtime()+"' or  is_abnormal(url) <> 0 ) " +
							" and to_char(to_date(substr(browsetime,0,10),'yyyy-mm-dd'),'yyyy-mm-dd') between '"+begin+"' and '"+end+"' "+
							" and computername like '"+usercode+"%' ");
			if(i+1<queryPara.size()){
				sql.append(" union all ");
			}
		}
		sql.append(" ) order by browsetime") ;
		return  this.jdbcTemplate.queryForList(sql.toString());
	}
	
	
	public  List<Map<String,Object>> queryFxbByUsercode(String usercode,String begin,String end){
		StringBuilder sql = new StringBuilder();
		sql.append("select  round(sum(changeTosecond(t.browsetime))/60/60,2) sc, "+
				    "   '' bl, ' ' fx , "+
				    "   get_titletype('',t.url) titlename"+
				 " from usertime t  "+
				" where t.computername like '"+usercode+"%' and substr(browsedate,0,10) between '"+begin+"' and '"+end+"' "+
				" group by get_titletype('',t.url),t.computername ");
		return this.jdbcTemplate.queryForList(sql.toString());
	}
	 
}
